
#include "dbManager.h"

using namespace std;

DBManager* DBManager::self = nullptr;
mutex DBManager::self_mutex;

DBManager* DBManager::getInstance()
{
    if(self == nullptr) {
        self_mutex.lock();
        if(self == nullptr) {
            self = new DBManager();
        }
        self_mutex.unlock();
    }
    return self;
}

DBManager::DBManager() : sql_conn(nullptr)
{
    sql_conn = mysql_init(NULL);
    auto ret = mysql_real_connect(sql_conn, "127.0.0.1", "li", "123456", 
                                  "stoneBook", 3306, NULL, CLIENT_MULTI_STATEMENTS);
    if(ret == nullptr) {
        cerr << "connect mysql failed.";
    }

    if(!isTableExist("main_type")) {
        if(createMainTypeTable()) {
            initIncomeType();
            initOutcomeMainType();
        }
    }
    if(!isTableExist("sub_type")) {
        if(createSubTypeTable()) {
            initOutcomeSubType();
        }
    }
}

DBManager::~DBManager()
{
}

void DBManager::init()
{
    cout << "DBManager::init()\n";
}

bool DBManager::isUserExist(const std::string &user)
{
    string sql = "select user_name from user where user_name = '" + user + "';";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    auto result = mysql_store_result(sql_conn);
    int row = mysql_num_rows(result);
    for(int i = 0; i < row; i++) {
        mysql_next_result(sql_conn);
    }
    mysql_free_result(result);
    return row > 0 ? true : false;
}

bool DBManager::registerUser(const std::string &user, 
                    const std::string &passwd, 
                    const std::string &phone, 
                    const std::string &email, 
                    const std::string &date)
{
    string sql = "insert into user values(@user_id + 1, '" + user + "', '" + passwd + "', '" + 
                  phone + "', '" + email + "', '" + date + "');";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

int DBManager::checkUserPassword(const std::string &user, const std::string &password)
{
    int id = -1;
    string sql = "select user_id from user where user_name = '" + user + "' and password = '" + password + "';";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return id;
    }
    auto result = mysql_store_result(sql_conn);
    int row = mysql_num_rows(result);
    if(row > 0) {
        auto data = mysql_fetch_row(result);
        id = atoi(data[0]);
        // 检查用户数据表是否存在
        if(!isDataTableExist(user))
            createDataTable(user);
    }
    mysql_free_result(result);
    return id;
}

bool DBManager::isTableExist(const std::string &table_name)
{
    string sql = "desc " + table_name  + ";";
    int ret = mysql_query(sql_conn, sql.c_str());
    if(ret != 0)
        return false;
    // 执行查询时，如果查询到数据必须要取出来，否则下次执行时报错
    auto result = mysql_store_result(sql_conn);
    int row = mysql_num_rows(result);
    while(!mysql_next_result(sql_conn)) {
        mysql_store_result(sql_conn);
    }
    mysql_free_result(result);
    return true;
}

bool DBManager::isDataTableExist(const std::string &user)
{
    string table_name = user + "_" + getCurrentYear();
    return isTableExist(table_name);
}

bool DBManager::createDataTable(const std::string &user)
{
    string tabel_name = user + "_" + getCurrentYear();
    string sql = "create table IF NOT EXISTS `" + tabel_name + "` ( \
                `id` INT UNSIGNED AUTO_INCREMENT, \
                `main_type_id` INT REFERENCES main_type(id), \
                `sub_type_id` INT REFERENCES sub_type(id), \
                `account_id` INT REFERENCES account(id), \
                `pay_type` CHAR(2) NOT NULL, \
                `money` DECIMAL NOT NULL, \
                `date`  DATETIME NOT NULL, \
                `link` VARCHAR(128), \
                `remark` VARCHAR(64), \
                PRIMARY KEY ( `id` ) \
            )ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

bool DBManager::createMainTypeTable()
{
    string sql = "create table IF NOT EXISTS `main_type` ( \
                `id` INT UNSIGNED AUTO_INCREMENT, \
                `user_id` INT NOT NULL REFERENCES user(user_id), \
                `pay_type` CHAR(2) NOT NULL, \
                `type` VARCHAR(64) NOT NULL, \
                `remark` VARCHAR(64), \
                PRIMARY KEY ( `id` ) \
            )ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

bool DBManager::addMainType(int user_id, bool income, const std::string &type)
{
    string sql = "insert into main_type (`id`, `user_id`, `pay_type`, `type`) values \
        (@id + 1, " + to_string(user_id) + ", " + (income ? "1" : "0") + ", '" + type + "');";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

bool DBManager::createSubTypeTable()
{
    string sql = "create table IF NOT EXISTS `sub_type` ( \
                `id` INT UNSIGNED AUTO_INCREMENT, \
                `main_type_id` INT NOT NULL REFERENCES main_type(id), \
                `type` VARCHAR(64) NOT NULL, \
                `remark` VARCHAR(64), \
                PRIMARY KEY ( `id` ) \
            )ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}


std::string DBManager::getCurrentYear()
{
    time_t now = time(NULL);
    tm *tm_t = localtime(&now);
    return to_string(tm_t->tm_year + 1900);
}

bool DBManager::initIncomeType()
{
    string sql = "insert into main_type (`id`, `user_id`, `pay_type`, `type`) values \
                                              (@id + 1, -1, 1, '工资'), \
                                              (@id + 1, -1, 1, '兼职外快'), \
                                              (@id + 1, -1, 1, '小生意'), \
                                              (@id + 1, -1, 1, '营业收入'), \
                                              (@id + 1, -1, 1, '二手转出'), \
                                              (@id + 1, -1, 1, '理财'), \
                                              (@id + 1, -1, 1, '转账'), \
                                              (@id + 1, -1, 1, '娱乐'), \
                                              (@id + 1, -1, 1, '利息'), \
                                              (@id + 1, -1, 1, '红包'), \
                                              (@id + 1, -1, 1, '销售款'), \
                                              (@id + 1, -1, 1, '退款返款'), \
                                              (@id + 1, -1, 1, '报销款'), \
                                              (@id + 1, -1, 1, '福利补贴'), \
                                              (@id + 1, -1, 1, '应收款'), \
                                              (@id + 1, -1, 1, '生活费'), \
                                              (@id + 1, -1, 1, '奖金'), \
                                              (@id + 1, -1, 1, '基金'), \
                                              (@id + 1, -1, 1, '股票'), \
                                              (@id + 1, -1, 1, '礼金'), \
                                              (@id + 1, -1, 1, '分红'), \
                                              (@id + 1, -1, 1, '租金'), \
                                              (@id + 1, -1, 1, '公积金'), \
                                              (@id + 1, -1, 1, '工程款'), \
                                              (@id + 1, -1, 1, '赔付款'), \
                                              (@id + 1, -1, 1, '漏记款'), \
                                              (@id + 1, -1, 1, '其他');";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

bool DBManager::initOutcomeMainType()
{
    string sql = "insert into main_type (`id`, `user_id`, `pay_type`, `type`) values \
                                              (@id + 1, -1, 0, '餐饮'), \
                                              (@id + 1, -1, 0, '交通'), \
                                              (@id + 1, -1, 0, '购物'), \
                                              (@id + 1, -1, 0, '娱乐'), \
                                              (@id + 1, -1, 0, '医教'), \
                                              (@id + 1, -1, 0, '居家'), \
                                              (@id + 1, -1, 0, '投资'), \
                                              (@id + 1, -1, 0, '人情'), \
                                              (@id + 1, -1, 0, '生意');";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return false;
    }
    return true;
}

bool DBManager::initOutcomeSubType()
{
    /**
     * 1. 查看主类是否存在，不存在则插入主类数据
     * 2. 获取主类 ID
     * 3. 插入子类数据
     */
    int main_type_id = isMainTypeExist(-1, 0, "餐饮");
    if(main_type_id == -1) {
        addMainType(-1, 0, "餐饮");
        main_type_id = isMainTypeExist(-1, 0, "餐饮");
    }
    string sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '早餐'), \
                        (@id + 1, " + to_string(main_type_id) + " , '午餐'), \
                        (@id + 1, " + to_string(main_type_id) + " , '晚餐'), \
                        (@id + 1, " + to_string(main_type_id) + " , '宵夜'), \
                        (@id + 1, " + to_string(main_type_id) + " , '零食'), \
                        (@id + 1, " + to_string(main_type_id) + " , '饮料水果'), \
                        (@id + 1, " + to_string(main_type_id) + " , '买菜原料'), \
                        (@id + 1, " + to_string(main_type_id) + " , '油盐酱醋'), \
                        (@id + 1, " + to_string(main_type_id) + " , '大米'), \
                        (@id + 1, " + to_string(main_type_id) + " , '餐饮其他');";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    
    main_type_id = isMainTypeExist(-1, 0, "交通");
    if(main_type_id == -1) {
        addMainType(-1, 0, "交通");
        main_type_id = isMainTypeExist(-1, 0, "交通");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '自行车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电动车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '摩托车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '打车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '加油'), \
                        (@id + 1, " + to_string(main_type_id) + " , '停车费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '车款车贷'), \
                        (@id + 1, " + to_string(main_type_id) + " , '车险'), \
                        (@id + 1, " + to_string(main_type_id) + " , '过路过桥'), \
                        (@id + 1, " + to_string(main_type_id) + " , '维修保养'), \
                        (@id + 1, " + to_string(main_type_id) + " , '罚款赔偿'), \
                        (@id + 1, " + to_string(main_type_id) + " , '驾照费用'), \
                        (@id + 1, " + to_string(main_type_id) + " , '公交'), \
                        (@id + 1, " + to_string(main_type_id) + " , '地铁'), \
                        (@id + 1, " + to_string(main_type_id) + " , '火车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '高铁'), \
                        (@id + 1, " + to_string(main_type_id) + " , '长途汽车'), \
                        (@id + 1, " + to_string(main_type_id) + " , '飞机'), \
                        (@id + 1, " + to_string(main_type_id) + " , '船舶'), \
                        (@id + 1, " + to_string(main_type_id) + " , '交通其他');";
    search_res = mysql_query(sql_conn, sql.c_str());

    
    main_type_id = isMainTypeExist(-1, 0, "购物");
    if(main_type_id == -1) {
        addMainType(-1, 0, "购物");
        main_type_id = isMainTypeExist(-1, 0, "购物");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '服饰鞋包'), \
                        (@id + 1, " + to_string(main_type_id) + " , '家居百货'), \
                        (@id + 1, " + to_string(main_type_id) + " , '宝宝用品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '化妆护肤'), \
                        (@id + 1, " + to_string(main_type_id) + " , '烟酒'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电子数码'), \
                        (@id + 1, " + to_string(main_type_id) + " , '文具'), \
                        (@id + 1, " + to_string(main_type_id) + " , '玩具'), \
                        (@id + 1, " + to_string(main_type_id) + " , '报刊书籍'), \
                        (@id + 1, " + to_string(main_type_id) + " , '珠宝首饰'), \
                        (@id + 1, " + to_string(main_type_id) + " , '家居家纺'), \
                        (@id + 1, " + to_string(main_type_id) + " , '保健用品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电器'), \
                        (@id + 1, " + to_string(main_type_id) + " , '摄影文印'), \
                        (@id + 1, " + to_string(main_type_id) + " , '购物其他');";
    search_res = mysql_query(sql_conn, sql.c_str());
    
    main_type_id = isMainTypeExist(-1, 0, "娱乐");
    if(main_type_id == -1) {
        addMainType(-1, 0, "娱乐");
        main_type_id = isMainTypeExist(-1, 0, "娱乐");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '旅游度假'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电影'), \
                        (@id + 1, " + to_string(main_type_id) + " , '网游电玩'), \
                        (@id + 1, " + to_string(main_type_id) + " , '麻将棋牌'), \
                        (@id + 1, " + to_string(main_type_id) + " , '洗浴足浴'), \
                        (@id + 1, " + to_string(main_type_id) + " , '运动健身'), \
                        (@id + 1, " + to_string(main_type_id) + " , '花鸟宠物'), \
                        (@id + 1, " + to_string(main_type_id) + " , '聚会玩乐'), \
                        (@id + 1, " + to_string(main_type_id) + " , '茶酒咖啡'), \
                        (@id + 1, " + to_string(main_type_id) + " , '卡拉OK'), \
                        (@id + 1, " + to_string(main_type_id) + " , '歌舞演出'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电视'), \
                        (@id + 1, " + to_string(main_type_id) + " , '娱乐其他');";
    search_res = mysql_query(sql_conn, sql.c_str());

    
    main_type_id = isMainTypeExist(-1, 0, "医教");
    if(main_type_id == -1) {
        addMainType(-1, 0, "医教");
        main_type_id = isMainTypeExist(-1, 0, "医教");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '书籍'), \
                        (@id + 1, " + to_string(main_type_id) + " , '网站'), \
                        (@id + 1, " + to_string(main_type_id) + " , '会员'), \
                        (@id + 1, " + to_string(main_type_id) + " , '医疗药品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '挂号门诊'), \
                        (@id + 1, " + to_string(main_type_id) + " , '养生保健'), \
                        (@id + 1, " + to_string(main_type_id) + " , '住院费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '养老院'), \
                        (@id + 1, " + to_string(main_type_id) + " , '学杂教材'), \
                        (@id + 1, " + to_string(main_type_id) + " , '培训考试'), \
                        (@id + 1, " + to_string(main_type_id) + " , '幼儿教育'), \
                        (@id + 1, " + to_string(main_type_id) + " , '学费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '家教补习'), \
                        (@id + 1, " + to_string(main_type_id) + " , '出国留学'), \
                        (@id + 1, " + to_string(main_type_id) + " , '助学贷款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '医教其他');";
    search_res = mysql_query(sql_conn, sql.c_str());

    
    main_type_id = isMainTypeExist(-1, 0, "居家");
    if(main_type_id == -1) {
        addMainType(-1, 0, "居家");
        main_type_id = isMainTypeExist(-1, 0, "居家");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '贷款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '家用小件'), \
                        (@id + 1, " + to_string(main_type_id) + " , '学习用品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '手机电话'), \
                        (@id + 1, " + to_string(main_type_id) + " , '水电燃气'), \
                        (@id + 1, " + to_string(main_type_id) + " , '生活费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '美容美发'), \
                        (@id + 1, " + to_string(main_type_id) + " , '住宿房租'), \
                        (@id + 1, " + to_string(main_type_id) + " , '材料建材'), \
                        (@id + 1, " + to_string(main_type_id) + " , '房款贷款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '快递邮政'), \
                        (@id + 1, " + to_string(main_type_id) + " , '电脑带宽'), \
                        (@id + 1, " + to_string(main_type_id) + " , '家政服务'), \
                        (@id + 1, " + to_string(main_type_id) + " , '物业'), \
                        (@id + 1, " + to_string(main_type_id) + " , '税费手续费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '消费贷款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '婚庆摄影'), \
                        (@id + 1, " + to_string(main_type_id) + " , '漏记款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '生活其他');";
    search_res = mysql_query(sql_conn, sql.c_str());
    
    main_type_id = isMainTypeExist(-1, 0, "投资");
    if(main_type_id == -1) {
        addMainType(-1, 0, "投资");
        main_type_id = isMainTypeExist(-1, 0, "投资");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '学习'), \
                        (@id + 1, " + to_string(main_type_id) + " , '利息'), \
                        (@id + 1, " + to_string(main_type_id) + " , '保险'), \
                        (@id + 1, " + to_string(main_type_id) + " , '出资'), \
                        (@id + 1, " + to_string(main_type_id) + " , '基金'), \
                        (@id + 1, " + to_string(main_type_id) + " , '股票'), \
                        (@id + 1, " + to_string(main_type_id) + " , 'P2P'), \
                        (@id + 1, " + to_string(main_type_id) + " , '理财产品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '投资贷款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '银行存款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '证券期货'), \
                        (@id + 1, " + to_string(main_type_id) + " , '外汇'), \
                        (@id + 1, " + to_string(main_type_id) + " , '贵金属'), \
                        (@id + 1, " + to_string(main_type_id) + " , '收藏品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '投资其他');";
    search_res = mysql_query(sql_conn, sql.c_str());
    
    main_type_id = isMainTypeExist(-1, 0, "人情");
    if(main_type_id == -1) {
        addMainType(-1, 0, "人情");
        main_type_id = isMainTypeExist(-1, 0, "人情");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '还钱'), \
                        (@id + 1, " + to_string(main_type_id) + " , '借出'), \
                        (@id + 1, " + to_string(main_type_id) + " , '礼金红包'), \
                        (@id + 1, " + to_string(main_type_id) + " , '物品'), \
                        (@id + 1, " + to_string(main_type_id) + " , '孝敬'), \
                        (@id + 1, " + to_string(main_type_id) + " , '请客'), \
                        (@id + 1, " + to_string(main_type_id) + " , '给予'), \
                        (@id + 1, " + to_string(main_type_id) + " , '待付款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '慈善捐款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '人情其他');";
    search_res = mysql_query(sql_conn, sql.c_str());

    
    main_type_id = isMainTypeExist(-1, 0, "生意");
    if(main_type_id == -1) {
        addMainType(-1, 0, "生意");
        main_type_id = isMainTypeExist(-1, 0, "生意");
    }
    sql = "insert into sub_type (`id`, `main_type_id`, `type`) values \
                        (@id + 1, " + to_string(main_type_id) + " , '进货采后'), \
                        (@id + 1, " + to_string(main_type_id) + " , '人工支出'), \
                        (@id + 1, " + to_string(main_type_id) + " , '材料辅料'), \
                        (@id + 1, " + to_string(main_type_id) + " , '办公费用'), \
                        (@id + 1, " + to_string(main_type_id) + " , '交通运输'), \
                        (@id + 1, " + to_string(main_type_id) + " , '工程付款'), \
                        (@id + 1, " + to_string(main_type_id) + " , '运营费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '会务费'), \
                        (@id + 1, " + to_string(main_type_id) + " , '营销广告'), \
                        (@id + 1, " + to_string(main_type_id) + " , '店面租金'), \
                        (@id + 1, " + to_string(main_type_id) + " , '注册登记'), \
                        (@id + 1, " + to_string(main_type_id) + " , '生意其他');";
    search_res = mysql_query(sql_conn, sql.c_str());
    return true;
}

int DBManager::isMainTypeExist(int user_id, bool income, const std::string &type)
{
    int id = -1;
    string sql = "select id from main_type where user_id = " + to_string(user_id) + 
                " and pay_type = " + (income ? "1" : "0") + " and type = '" + type + "';";
    auto search_res = mysql_query(sql_conn, sql.c_str());
    if(mysql_errno(sql_conn) != 0) {
        cerr << "[sql] " << sql << " [err]:" << mysql_error(sql_conn) << endl;
        return id;
    }
    auto result = mysql_store_result(sql_conn);
    int row = mysql_num_rows(result);
    if(row > 0) {
        auto data = mysql_fetch_row(result);
        id = atoi(data[0]);
    }
    mysql_free_result(result);
    return id;
}